# coding=utf-8
# sqlserver的连接
import pymssql
import sqlalchemy
from sqlalchemy import create_engine


class MSSQL:
    def __init__(self, host, user, pwd, db, server, port):

        self.host = host
        self.user = user
        self.pwd = pwd
        self.db = db
        self.server = server
        self.port = port

    def __new__(cls, *args, **kwargs):
        if not hasattr(cls, '_instance'):
            cls._instance = super(MSSQL, cls).__new__(cls)
        return cls._instance

    @property
    def __rc(self):
        """
        得到连接信息
        返回: conn.cursor()
        """

        if not self.db:
            raise (NameError, "没有设置数据库信息")
        connection_format = 'mssql+pymssql://{0}:{1}@{2}/{3}?charset=utf8'
        connection_str = connection_format.format(self.user, self.pwd, self.host, self.db)
        engine = create_engine(connection_str, echo=False)
        connection = engine.connect()
        if not connection:
            raise (NameError, "连接数据库失败")
        else:
            return connection

    def ExecQuery(self, sql):
        """
        执行查询语句
        返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段
        """

        result = self.__rc.execute(sql)
        # 查询完毕后必须关闭连接
        resultList = [dict(zip(result.keys(), res)) for res in result.fetchall()]
        self.__rc.close()
        return resultList


"""
示例如下
"""
# def main():
#     # host默认为127.0.0.1，如果打开了TCP动态端口的需要加上端口号，如'127.0.0.1:1433'
#     # user默认为sa
#     # pwd为自己设置的密码
#     # db为数据库名字
#     ms = MSSQL(host='192.168.32.191', user="sa", pwd="123456", db="AXData")
#     resList = ms.ExecQuery("SELECT * FROM TControl")
#     print(resList)

# ms = MSSQL(host='192.168.10.231', server='192.168.10.231\SQLEXPRESS', user="sa", pwd="123456", db="AxData", port=1433)
# if __name__ == '__main__':
# resultList = ms.ExecQuery("SELECT * FROM TEventType")
# print(resultList)

# res = ms.ExecQuery("SELECT * FROM TControl")
# dtb_access_control_list = []
# for i in res:
#     # print(i['DoorOpen'])
#     status = str(bin(i['DoorOpen'])[2:].rjust(8, '0'))[::-1]
#     # print(status)
#     doorlist = ms.ExecQuery("SELECT * FROM TDoor WHERE ControlID = '%s'" % i['ControlID'])
#     for door in doorlist:
#         door["state"] = '关门' if status[doorlist.index(door)] == '1' else '开门'
#         print(door)
#         dict_ = {
#             "name": door['DoorName'],
#             "devSn": door['DoorID'],
#             "state": "在线",
#             "switche_state": door["state"],
#             "warn_access_state": door["AlarmMast"]
#         }
#         dtb_access_control_list.append(dict_)
# print(dtb_access_control_list)

# print(bin(3)[2:].rjust(8, '0'))

#     select * from TDoor inner join TControl on TDoor.ControlID=TControl.ControlID
# inner join TArea on TControl.AreaID= TArea.AreaID
# 查询所有的门的实时状态

#     select DoorOpen from TControl
# inner join TArea on TControl.AreaID= TArea.AreaID
# DoorOpen的值为实时门状态
# 分别按2进制进行取位，从低到高前4位分别表示1, 2,3,4门的状态
# Bit0 门1:0x01
# Bit1 门2:0x02
# Bit2 门3:0x04
# Bit3 门4:0x08
# 当门磁和锁输出都关闭的时候，输出为1表示关，否则输出0表示开
# m = bin(a)

# TAlarm	报警事件
# TEvent	刷卡事件
# TEventType	事件类型
# TDoor
#     resList = [1, 2, 3, 4, 5, 6]
#     for index,value in enumerate(resList):
#         print(index,value)

# 十进制转二进制补位0
# bin(10)[2:].rjust(8, '0')
